package com.sendi.system.util.export;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

 
/**
 * 报表统计类,使用Excel形式
 * 
 * @author wanglang 设计各种形式的报表
 */
public class ReportTooler
{

	String sheetName;

	HttpServletResponse response;

	WritableWorkbook wwb;


	WritableSheet sheet;
	WritableCellFormat format;

	Label label ;
	

	public ReportTooler()
	{
	}

	public ReportTooler(String sheetName, HttpServletResponse response)
	{
		this.sheetName = sheetName;
		this.response = response;

	}
    

	public void ReportInit()
	{
		response.setContentType("application/x-msdownload");
		response.setContentType("application/msexcel");
		try
		{
			response.setHeader("Content-Disposition", "attachment;" + " filename=" + new String(sheetName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
			wwb = Workbook.createWorkbook(response.getOutputStream());
			sheet = wwb.createSheet(sheetName, 0);
		 
			format = new WritableCellFormat();
			format.setAlignment(Alignment.JUSTIFY); // 设置对齐
			 format.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_50); // 设置边框线
		 
			format.setWrap(true);//自动换行
			// 设置单元格的背景颜色
			// format.setBackground(jxl.format.Colour.RED);
		 
		}
		catch (UnsupportedEncodingException e1)
		{

			e1.printStackTrace();
		}

		catch (IOException e)
		{

			e.printStackTrace();
		}
		catch (WriteException e)
		{
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}

	}

	 
	/*
	 * 
	 * 数据源为list对像的导出
	 */
	
	public void ReportWithList(List list,String topic, String[] title,String datformat,String fileds[]) throws RowsExceededException, WriteException
	{
		setHeadFoot(topic,title);
		int sourcesize=list.size();
	 	int filelength=fileds.length;
	
		try
		{
		 
		 
			java.text.SimpleDateFormat sf=new SimpleDateFormat(datformat);
			int col=0,row=2;
			for(Object o :list)
			{
				String content="";
				Class c=o.getClass();
				for(int k=0;k<filelength;k++)
				{
					  Field fi=c.getDeclaredField(fileds[k]);
					  if(fi==null)
					  {content=fileds[k]+"未找到";}
					  else
					  {
						  fi.setAccessible(true);
						  Object fildobject=fi.get(o);
						  if(fildobject instanceof java.util.Date)
						  {
							  Date date=(Date)o;
							  content=date==null?"":sf.format(date);
						  }
						  else
						  {
							  content=fildobject==null?"":fildobject.toString();
							  content=(content==null||content.trim().length()==0)?"":content;
						  }
					  }
					  
					  
						label = new Label(col, row, content, format);
						if(!content.equals("")&&content.length()>=10)
						{
							sheet.setColumnView(col, content.length());//如果内容的长度>=10设置列宽为内容的长度
						}
						 sheet.addCell(label);   
						col++;
						
						
				}
				
				row++;
				col=0;
			}
		
			// 写入数据   
            wwb.write();   
            // 关闭文件   
            wwb.close();   
		}
	
		catch (IOException e)
		{
			
			e.printStackTrace();
		} catch (SecurityException e) {
			 
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
		 
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
		 
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			 
			e.printStackTrace();
		}
		 
		
		

	}
	 
 
	 
 
	 
	/*
	 * 
	 * 数据源为list对像的导出
	 */
	
	public void ReportWithList(List list,String topic, String[] title,String datformat,String fileds[],String secTop[]) throws RowsExceededException, WriteException
	{
		setHeadFoot(topic,title,secTop);
		int sourcesize=list.size();
	 	int filelength=fileds.length;
	
		try
		{
		 
		 
			java.text.SimpleDateFormat sf=new SimpleDateFormat(datformat);
			int col=0,row=3;
			for(Object o :list)
			{
				String content="";
				Class c=o.getClass();
				for(int k=0;k<filelength;k++)
				{
					  Field fi=c.getDeclaredField(fileds[k]);
					  if(fi==null)
					  {content=fileds[k]+"未找到";}
					  else
					  {
						  fi.setAccessible(true);
						  Object fildobject=fi.get(o);
						  if(fildobject instanceof java.util.Date)
						  {
							  Date date=(Date)o;
							  content=date==null?"":sf.format(date);
						  }
						  else
						  {
							  content=fildobject==null?"":fildobject.toString();
							  content=(content==null||content.trim().length()==0)?"":content;
						  }
					  }
					  
					  
						label = new Label(col, row, content, format);
						if(!content.equals("")&&content.length()>=10)
						{
							sheet.setColumnView(col, content.length());//如果内容的长度>=10设置列宽为内容的长度
						}
						 sheet.addCell(label);   
						col++;
						
						
				}
				
				row++;
				col=0;
			}
		
			// 写入数据   
            wwb.write();   
            // 关闭文件   
            wwb.close();   
		}
	
		catch (IOException e)
		{
			
			e.printStackTrace();
		} catch (SecurityException e) {
			 
			e.printStackTrace();
		} catch (NoSuchFieldException e) {
		 
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
		 
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			 
			e.printStackTrace();
		}
		 
		
		

	}
	
	

	/**
	 * 设置头标题的格式
	 * @return
	 */
	public WritableCellFormat getTopicformat()
	{
		 WritableCellFormat font =null;
		  try
		{
		   jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("楷书"),15);  
		 	   font = new WritableCellFormat(wfont);   
			font.setAlignment(Alignment.CENTRE);
			
		}
		catch (WriteException e)
		{
			 
			e.printStackTrace();
		}
          return font;
          
	}
	
	public WritableCellFormat getSecTopicformat()
	{
		 WritableCellFormat font =null;
		  try
		{
		   jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("楷书"),12);  
		 	   font = new WritableCellFormat(wfont);   
			font.setAlignment(Alignment.LEFT);
			
		}
		catch (WriteException e)
		{
			 
			e.printStackTrace();
		}
          return font;
          
	}
	
	
	/**
	 * 设置头标题的格式
	 * @return
	 */
	public WritableCellFormat getTitleformat()
	{
		 WritableCellFormat font =null;
		  try
		{
		   jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("宋体"),12);  
		 	   font = new WritableCellFormat(wfont);   
		 	  wfont.setBoldStyle(WritableFont.BOLD);
			font.setAlignment(Alignment.CENTRE);
			font.setBackground(Colour.YELLOW2);
			font.setAlignment(Alignment.JUSTIFY); // 设置对齐
			font.setBorder(Border.ALL, BorderLineStyle.THIN,Colour.GRAY_50); // 设置边框线
			 
			font.setWrap(true);//自动换行
			 
		}
		catch (WriteException e)
		{
			 
			e.printStackTrace();
		}
          return font;
          
	}
	
	
	public boolean indatacol(int i,String [] datacol,String colname[])
	{
		
		int len=datacol.length;
		
		for(int j=0;j<len;j++)
		{
			
			if(colname[i].equalsIgnoreCase(datacol[j]))
			{
				 
				return true;
			}
				
		}
		return false;
		 

	}
	public boolean indoublecol(int i,String [] doublecol,String colname[])
	{
		int len=doublecol.length;
		for(int j=0;j<len;j++)
		{
			if(colname[i].equalsIgnoreCase(doublecol[j]))
			{
				return true;
			}
				
		}
		return false;
	}
	
	public boolean inSumcol(int i,String colname[],String [] sumcol )
	{
		int len=sumcol.length;
			for(int j=0;j<len;j++)
			{
				if(colname[i].equalsIgnoreCase(sumcol[j]))
				{
					return true;
				}
			}
			return false;
			
	}
	public String getChar(int i) 
	{
		return String.valueOf((char)(i+64));
		
	}
	
	public void setHeadFoot(String topic, String[] title) throws RowsExceededException, WriteException
	{
		
		ReportInit();//初始化	
		//设置标题 A4打印是9列   excel中都在先列才是行而且下标从0开始
		int len=title.length;
		sheet.mergeCells(0, 0, len-1, 0);
		WritableCellFormat font=getTopicformat();
		
		label = new Label(0, 0, topic,font);
		 sheet.addCell(label);   
		 WritableCellFormat titlef= getTitleformat();
		for (int i = 0; i <len;  i++)
		{
			// Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
			// 在Label对象的子对象中指明单元格的位置和内容
			label = new Label(i, 1, title[i], titlef);
		 	// 将定义好的单元格添加到工作表中
			sheet.addCell(label);

		}
			
	}
	/*
	 * secTop 是二级标题，用来在报表导出时，显示统计时间
	 */
	public void setHeadFoot(String topic, String[] title,String secTop[]) throws RowsExceededException, WriteException
	{
		
		ReportInit();//初始化	
		//设置标题 A4打印是9列   excel中都在先列才是行而且下标从0开始
		int len=title.length;
		sheet.mergeCells(0, 0, len-1, 0);
		WritableCellFormat font=getTopicformat();
		
	 	label = new Label(0, 0, topic,font);
		 sheet.addCell(label);   
		 WritableCellFormat secfont=getSecTopicformat();
		 WritableCellFormat titlef= getTitleformat();
		 boolean isaddsec=false;
	     if(secTop!=null)
		  {
			  int lent=secTop.length;
			  isaddsec=lent>0?true:false;
				 for (int i = 0; i <lent;  i++)
					{
						// Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
						// 在Label对象的子对象中指明单元格的位置和内容
						label = new Label(i, 1, secTop[i], secfont);
					 	// 将定义好的单元格添加到工作表中
						sheet.addCell(label);
	                  
					}
	 	  }
	 	int r= isaddsec?2:1;
 		for (int i = 0; i <len;  i++)
		{
			// Label(x,y,z)其中x代表单元格的第x+1列，第y+1行, 单元格的内容是y
			// 在Label对象的子对象中指明单元格的位置和内容
			label = new Label(i, r, title[i], titlef);
		 	// 将定义好的单元格添加到工作表中
			sheet.addCell(label);

		}
		
		
			
	}
	
	/**
	 * 获取Date数据类型的列
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	
	public String[] getDatecol(java.sql.ResultSet rs) throws SQLException
	{
		ResultSetMetaData mt=rs.getMetaData();
		int colcount=mt.getColumnCount();
		String datecol[]=new String[colcount];
	 
		int j=0;
		for(int i=1;i<=colcount;i++)
		{
			
			
		 
			if(mt.getColumnTypeName(i).equalsIgnoreCase("DATE"))
			{
				datecol[j]=mt.getColumnName(i);
				
			}
			j++;
		}
		 
		return datecol;
	}
	
	/**
	 * 获取Double数据类型的列
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	
	public String [] getDoubleCol(java.sql.ResultSet rs)  throws SQLException
	{
		ResultSetMetaData mt=rs.getMetaData();
		int colcount=mt.getColumnCount();
		String doucol[]=new String[colcount];
		int j=0;
		for(int i=1;i<=colcount;i++)
		{
			
			String coltype=mt.getColumnTypeName(i);
			if(coltype.equalsIgnoreCase("DOUBLE")||coltype.equalsIgnoreCase("FLOAT"))
			{
				doucol[j]=mt.getColumnName(i);
			}
			j++;
		}
		return doucol;
	}
	
	
	
	/**
	 * 数值类型的格式
	 * @param format 如:#.### 
	 * @return
	 */
	public WritableCellFormat numberFormat(String format)
	{
		 jxl.write.NumberFormat nf = new jxl.write.NumberFormat(format);   
         jxl.write.WritableCellFormat  formats = new jxl.write.WritableCellFormat(nf);  
         try
		{
			formats.setBorder(Border.ALL, BorderLineStyle.THIN);
			 formats.setAlignment(Alignment.CENTRE);
		}
		catch (WriteException e)
		{
			 
			e.printStackTrace();
		}
        
         return formats;
	}
	
	public WritableCellFormat getCommFormat()
	{
		 jxl.write.WritableCellFormat  format = new jxl.write.WritableCellFormat();  
         try
		{
			format.setBorder(Border.ALL, BorderLineStyle.THIN);
			 format.setAlignment(Alignment.CENTRE);
		}
		catch (WriteException e)
		{
			 
			e.printStackTrace();
		}
        
         return format;
	}
 
	
}
